package services.AZ20;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLConnection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Vector;

import javax.servlet.http.HttpServletResponse;
import org.apache.log4j.Level;
import org.apache.log4j.Logger;
import org.apache.log4j.chainsaw.Main;
import vo.system.AZ20;
import com.Const;
import com.PublicSystem;
import com.util.Util;
/**
 * 领卡相关操作Service
 * @author duy
 *
 */
public class AZ20Services {
	PublicSystem sys = PublicSystem.getInstance();
	/**
	 * 查询制卡信息（导出）
	 * @author duy
	 * @param obj
	 * @param parms
	 * @throws Exception
	 */
	public HashMap getAZ201(Object[] obj, HashMap parms) throws Exception {
		String sql = "select AAB001,AAC001,AAC003,AAC004,AAC002,aaz501,aac005,caz002,aae008_name from v_az201 where " +
					 "('" + obj[0] +
					 "' is null or aaz001 = ?) and " + "('" + obj[1] +
					 "' is null or aae008 = ?)";
		HashMap map = sys.getGrid(sql, obj, parms, false);
		return map;
	}
	/**
	 * 查询回卡信息
	 * @author duy
	 * @param obj
	 * @param parms
	 * @throws Exception
	 */
	public HashMap gettemp(Object[] obj, HashMap parms) throws Exception {
		String sql = "select * from v_zjtmp where eventid = ? ";			
		HashMap map = sys.getGrid(sql, obj, parms, false);
		return map;
	}
	/**
	 * 查询制卡信息
	 * @author duy
	 * @param obj
	 * @param parms
	 * @throws Exception
	 */
	public HashMap getAZ20(Object[] obj, HashMap parms) throws Exception {
		String sql = "select aab001,aac001,aac003,(select AAA103 from AA10 where AAA102 = t.aac004 and " +
				"AAA100 = 'AAC004') as aac004,aac002,(select AAA103 from AA10 where AAA102 = t.aac005 and " +
				"AAA100 = 'AAC005') as aac005,aaz501,(select caz002 from az17 where aac001=t.aac001 and aaz001=t.aaz001) as caz002 ," +
				"(select AAA103 from AZ11 where AAA102 = t.aae008 and " +
				"AAA100 = 'bank') as aae008 from az20 t where " + 
				"('" +  obj[0] + "' is null or aaz001 = ?) and ('" + obj[1] + "' is null or aae008 = ?)" ;
		HashMap map = sys.getGrid(sql, obj, parms, false);
		return map;
	}
	/**
	 * 生成批次信息，利用存储过程获取
	 * @author duy
	 * @param obj
	 * @throws Exception
	 */
	public String insertAZ20(HashMap obj) throws Exception {
		AZ20 vo = new AZ20();
		vo = (AZ20) Util.MapToVo(vo, obj);
		String ss = sys.callDBFuncProce("ic_az20.set_zksj", "function", obj);
		String[] str = ss.split("&");
		System.out.println(str);
		if ("0".equals(str[0]) || null == str[0]) {
			return "制卡数据已生成!";
		} else {
			return "制卡数据生成失败:原因为" + str[0];
		}
	}
	/**
	 * 数据导出,下载
	 * @author duy
	 * @param obj
	 * @param parms
	 * @param response 
	 * @throws Exception
	 */
	public File ExpAZ20(Object[] obj, HashMap parms, HttpServletResponse response) throws Exception {
		String sql = "select AAB001,AAC001,AAC003,AAC004,AAC002,aac005,aaz501,caz002,aae008_name from v_az201 where " + 
					 "aaz001 = '" + obj[0] + "' and " + "aae008 = '" + obj[1] + "'";
		String url=Const.server_path + "/export_sjsc/sjsc" + obj[0]+ obj[1] + "/";
		File f = new File(url+obj[0]+ obj[1] +".xls");
		//如果父目录不在就创建
		if(!f.getParentFile().exists()) {
			f.getParentFile().mkdirs();
		}	
		if((!f.exists())) {
			f.createNewFile();
		}
		sys.writeExcelTitle(new FileOutputStream(f), sql, new String[] { "劳动保障证号", "社会保障卡号", "姓名", "性别", "证件号",
																		 "民族","卡识别号", "通卡卡面号", "制卡银行" });
		/*生成照片信息*/
		Vector v=sys.SelResultVec("select aac001 from v_az201 where aaz001 = '" + obj[0] + "' and " + "aae008 = '" + obj[1] + "'");
		String aac001="";
		
		if(null!=v){
			sys.BeginTransaction();
			for(int i=0;i<v.size();i++){
				aac001=((Vector)v.elementAt(i)).elementAt(0).toString();
				sys.selectBlobTransaction("select aac100 from az02 where aac001='"+aac001+"'", url+aac001+".JPG");	
			}
			sys.endTransaction();
		}
		
		String sourceFilePath=url;
		String zipFilePath=url;
		String fileName=obj[0].toString()+ obj[1].toString();
		boolean flag=Util.fileToZip(sourceFilePath, zipFilePath, fileName);
		if(flag){
			File zip = new File(url+obj[0]+ obj[1] +".zip");
			return zip;
		}
		else{
			return null;
		}
	}
	/**
	 * 数据导出,下载
	 * @author duy
	 * @param obj
	 * @param parms
	 * @param response 
	 * @throws Exception
	 */
	public File exkcdate(String id) throws Exception {
		String sql = "select c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11 from zj_tmp where execcode='-1' and EVENTID ='" + id + "'";
		File f = new File(Const.server_path + "/export_sjhp/sjhp"+ id +".xls");
		//如果父目录不在就创建
		if(!f.getParentFile().exists()) {
			f.getParentFile().mkdirs();
		}	
		if((!f.exists())) {
			f.createNewFile();
		}		
		sys.writeExcelTitle(new FileOutputStream(f), sql, new String[] { "姓名", "性别", "民族", "个人编号", "身份证", "盒号", 
																		 "卡识别号", "通卡卡面号", "通卡卡内号","银行","银行账号" });
		return f;
	}
	/**
	 * 下拉框数据取得
	 * @author duy
	 * @param
	 * @throws Exception
	 */
	public List getSelect(String AAA100) throws Exception {
		String sql = "SELECT AAA102,AAA103 FROM AZ11 where AAA100 =? ";
		List data = sys.query(sql, new Object[] { AAA100 }, true);
		return data;
	}
	/**
	 * 银行下拉框选项
	 * @param sj
	 * @return
	 * @throws Exception
	 */
	public List getYinhang(String sj) throws Exception {
		String sql = "select aaa103,aaa102 from v_ic_az11 where aaa100=?";
		List data = sys.query(sql, new Object[] {sj}, true);
		return data;
	}
	/**
	 * 校验导入
	 * @param request
	 * @param response
	 * @throws Exception
	 */
	public HashMap checkkcdata(HashMap obj) throws Exception{	 	
		String ss = sys.callDBFuncProce("ic_az20.chk_kcdata", "function", obj).toString();
		String[] str = ss.split("&");	
		String result = str[0];
		HashMap map = new HashMap();
		if("0".equals(result)) {
			String res = str[1];
			String str1 = sys.SelResultStr("select chr(1) from dual");
		    String str2 = sys.SelResultStr("select chr(2) from dual");
		    String sss[] = res.split(str1);
		    for (int i = 0; i < sss.length; i++) {
		    	String aa[] = sss[i].split(str2);
		    	map.put(aa[0], aa[1]);
			}			
		}
		map.put("result", result);
		return map;
	} 
	/**
	 * 登记回盘数据
	 * @param request
	 * @param response
	 * @throws Exception
	 */
	public String savekcdata(HashMap obj) throws Exception{	 	
		String ss = sys.callDBFuncProce("ic_az20.set_kcdata", "function", obj).toString();
		String[] str = ss.split("&");	
		String result = str[0];				
		return result;
	}
	/**
	 *  获取银行下拉框选项
	 * @param obj
	 * @param parms
	 * @return 
	 * @throws Exception
	 */
	public HashMap  getbank(Object[] obj, HashMap parms) throws Exception {
		String sql = "select distinct (select aaa103  from v_ic_az11 where aaa102=t.aae008 and aaa100='bank')as aae0081 " +
				"from az20 t where aaz001=?";
		HashMap map = sys.getGrid(sql, obj, parms, false);
		return map;
	}
	/**
	* 查询各网点领卡数量
	* @author duy
	* @param request
	* @param response
	*@throws Exception
	*/ 
	public HashMap  getlksl(String aaz001, String lklx, String aaz730) throws Exception {
		String sql = "select distinct  aae008 from az20 t where aaz001='" + aaz001 + "'";
		List data = sys.query(sql, new Object[] {}, true);
		HashMap map = new HashMap();
		HashMap map1 = new HashMap();
		List list = new ArrayList();
		String ss = "";
		for(int i = 0; i < data.size(); i++){
			map = (HashMap) data.get(i);
			map.put("aaz001", aaz001);
			map.put("lklx", lklx);
			map.put("aaz730", aaz730);
			ss = sys.callDBFuncProce("ic_az20.get_lksl", "function", map).toString();
			String[] str = ss.split("&");
			String str2 = sys.SelResultStr("select chr(2) from dual");
			String[] str1 = str[1].split(str2);
			map.put("lksl", str1[1]);
			list.add(map);
		}
		map1.put("total", data.size());
		map1.put("data", list);
		return map1;
	}
	/**
	 * 领卡类型
	 * @param aaz001
	 * @param lklx
	 * @param aaz730
	 * @return
	 * @throws Exception
	 */
	public HashMap  getlksl1(String aaz001, String lklx, String aaz730) throws Exception {
		String sql = "select distinct  aae008,(select aaa103  from v_ic_az11 where aaa102=t.aae008 and " +
					 "aaa100='bank')as aae0081 from az20 t where aaz001='" + aaz001 + "'";
		List data = sys.query(sql, new Object[] {},true);
		HashMap map = new HashMap();
		HashMap map1 = new HashMap();
		List list = new ArrayList();
		String ss = "";
		for(int i = 0; i < data.size(); i++){
			map = (HashMap) data.get(i);
			map.put("aaz001", aaz001);
			map.put("lklx", lklx);
			map.put("aaz730", aaz730);
			ss = sys.callDBFuncProce("ic_az20.get_lksl", "function", map).toString();
			String[] str = ss.split("&");
			String str2 = sys.SelResultStr("select chr(2) from dual");
			String[] str1 = str[1].split(str2);
			map.put("lksl", str1[str1.length - 1]);
			list.add(map);
		}
		map1.put("total", data.size());
		map1.put("data", list);
		return map1;
	}
	/**
	 * 领卡登记
	 * @param aaz001
	 * @param lklx
	 * @param aaz730
	 * @param aaz7301
	 * @param aae011
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public String setlkdj(String aaz001, String lklx, String aaz730,
			String aaz7301, String aae011, String id) throws Exception {
		HashMap map = new HashMap();
		String aa[] = id.split(",");
		map.put("aaz001", aaz001);
		map.put("lklx", lklx);
		map.put("aaz730", aaz7301);
		map.put("aae011", aae011);
		for(int i = 0; i < aa.length; i++) {
		    map.put("aae008", aa[i]);
		    sys.callDBFuncProce("ic_az20.set_lkdj", "function", map).toString();
		}
		return "操作成功！";
	}
}